package com.trying.tryCore.query.factory;

import com.trying.tryCore.query.QueryObject;
import com.trying.tryCore.query.api.QueryOperator;
import org.apache.commons.lang.StringUtils;

import java.util.Date;
import java.util.List;

/**
 * Created by Tearslee on 2017/8/15.   base for sqlserver 2012
 */
public class SqlServerFactory extends AbstractQueryFactory{
    protected String buildLikeValue(Object obj) {
        String value = obj.toString();
        if (StringUtils.isEmpty(value)) {
            return value;
        }
        // 转义反斜杠
        if (StringUtils.contains(value, '\\')) {
            value = value.replaceAll("\\\\", "\\\\\\\\\\\\\\\\");
        }
        // 转义_%?
        if (StringUtils.containsAny(value, "%_")) {
            value = value.replaceAll("([%_])", "\\\\$1");
        }
        return value;
    }

    protected void buildDateCondition(StringBuilder sql, List<Object> params, String fieldName, QueryOperator op, Date value) {
        sql.append(" CAST(CAST(").append(fieldName).append(" as datetime) as integer)")
                .append(op.getSymbol()).append(" CAST(CAST(? as datetime) as integer)");
        params.add(value);
    }


    /*
    *  protected void buildPageSql(StringBuilder sql, List<Object> params, QueryObject<?> query) {
        sql.append("SELECT * FROM(SELECT row_number () OVER (ORDER BY tempcol) temprow,duang * FROM(SELECT TOP ? tempcol = 0 ,* FROM ");
        params.add(query.page() * query.pageSize());
        this.buildSql(sql, params, query);
        sql.append(") duang) table_alias WHERE table_alias.temprow > ?");
        params.add((query.page() - 1) * query.pageSize());
    }
    * */

    @Override
    protected void buildCountSql(StringBuilder sql, List<Object> params, QueryObject<?> query) {
        if (StringUtils.isNotEmpty(query.groupBy())) {
            // 含有group by子句，使用查询方式查询
            sql.append("select count(1) from (");
//            String[] groupCol=query.groupBy().split(",");
//            for (String str:groupCol) {
//                sql.append(" CAST("+str+" AS VARCHAR )");
//            }
            sql.append("select ");
            sql.append(" 1 as countCol");
            sql.append(" from ");
            sql.append(query.from());
            if ( ! query.conditions().isEmpty()) {
                sql.append(" where ");
                this.buildConditions(sql, params, query);
            }
            if (StringUtils.isNotEmpty(query.groupBy())) {
                sql.append(" group by ").append(query.groupBy());
                if(StringUtils.isNotEmpty(query.having())) {
                    sql.append(" having ").append(query.having());
                }
            }
            sql.append(") countTable");
        } else {
            // 不含有group by子句，使用count(*)方式查询
            if (query.distinct()) {
                sql.append("select count(distinct ").append(query.select()).append(")");
            } else {
                sql.append("select count(*)");
            }
            sql.append(" from ");
            sql.append(query.from());
            if ( ! query.conditions().isEmpty()) {
                sql.append(" where ");
                buildConditions(sql, params, query);
            }
        }
    }

    /**
     * 针对sqlserver2012 的分页,OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
     * *****    必须要有orderBy语句       ***
     * @param sql
     * @param params
     * @param query
     */
    protected void buildPageSql(StringBuilder sql, List<Object> params, QueryObject<?> query) {
        this.buildSql(sql, params, query);
        if(query.orders()==null || query.orders().size()<1){
            sql.append(" ORDER BY 1 DESC");
        }
        sql.append(" OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ");
        params.add((query.page() - 1) * query.pageSize());
        params.add(query.page() * query.pageSize());
    }

    protected void buildTopSql(StringBuilder sql, List<Object> params, QueryObject<?> query, int top) {
        sql.append(" select top "+top+" temp.* from ( ");
        this.buildSql(sql, params, query);
        sql.append(") temp");
    }
}
